from collections import OrderedDict

from rest_framework.pagination import PageNumberPagination
from rest_framework.response import Response
from django.db import connection
from django.core.paginator import Paginator


class MyPageNumberPagination(PageNumberPagination):
    page_size = 1000
    page_size_query_param = 'pagesize'
    page_query_param = 'page'

    def get_paginated_response(self, data):
        return Response(OrderedDict([
            ('code', 200),
            ('msg', '查询成功'),
            ('data', data)
        ]))


def fetchone_sql(sql, params=None, db='default', flat=False):
    """
    返回一行数据
    :param sql: sql语句
    :param params: sql语句参数
    :param db: Django数据库名
    :param flat: 如果为True，只返回第一个字段值，例如：id
    :return: 例如：(id, 'username', 'first_name')
    """
    cursor = connection.cursor()
    cursor.execute(sql, params)
    fetchone = cursor.fetchone()
    cursor.close()
    if fetchone:
        fetchone = fetchone[0] if flat else fetchone
    return fetchone


def fetchone_to_dict(sql, params=None, db='default'):
    """
    返回一行数据
    :param sql: sql语句
    :param params: sql语句参数
    :param db: Django数据库名
    :return: 例如：{"id": id, "username": 'username', "first_name": 'first_name'}
    """
    cursor = connection.cursor()
    cursor.execute(sql, params)

    columns = [col[0] for col in cursor.description]
    row = [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]
    # desc = cursor.description
    # row = dict(zip([col[0] for col in desc], cursor.fetchone()))
    cursor.close()
    return row


def fetchall_sql(sql, params=None, db='default', flat=False):
    """
    返回全部数据
    :param sql: sql语句
    :param params: sql语句参数
    :param db: Django数据库名
    :param flat: 如果为True，只返回每行数据第一个字段值的元组，例如：(id1, id2, id3)
    :return: 例如：[(id, 'username', 'first_name')]
    """
    cursor = connection.cursor()
    cursor.execute(sql, params)
    fetchall = cursor.fetchall()
    cursor.close()
    if fetchall:
        fetchall = tuple([o[0] for o in fetchall]) if flat else fetchall
    return fetchall


def fetchall_to_dict(sql, params=None, db='default'):
    """
    返回全部数据
    :param sql: sql语句
    :param params: sql语句参数
    :param db: Django数据库名
    :return: 例如：[{"id": id, "username": 'username', "first_name": 'first_name'}]
    """
    cursor = connection.cursor()
    cursor.execute(sql, params)
    columns = [col[0] for col in cursor.description]
    object_list = [
        dict(zip(columns, row))
        for row in cursor.fetchall()
    ]
    return object_list
    # desc = cursor.description
    # object_list = [
    #     dict(zip([col[0] for col in desc], row))
    #     for row in cursor.fetchall()
    # ]
    # cursor.close()
    # return object_list


class QueryWrapper:
    """查询集包装器。实现django Paginator需要的必要方法，实现和query一样使用Paginator分页"""

    def __init__(self, sql, model_class=None):
        """
        :param sql: sql语句
        """
        self.sql = sql.replace(';', '')
        self.model_class = model_class

    def count(self):
        """计算总页数"""
        sql = """select count(*) as count from (%s) _count""" % self.sql
        data = fetchone_to_dict(sql)
        if data:
            return data[0]['count']
        return 0

    def __getitem__(self, k):
        x, y = k.start, k.stop
        sql = self.sql + ' LIMIT {num} OFFSET {start}'.format(start=x, num=y - x)
        result = fetchall_to_dict(sql)
        if self.model_class:
            result = [self.model_class(**item) for item in result]
        return result

    def all(self):
        return fetchall_sql(self.sql)


